The BAAN application object has the following properties:

Name	Type	R/W
Timeout	long	R/W
FunctionCall	string	R/W
Error	long	R
ReturnValue	string	R

These methods and properties allow Visual Basic programmers to call any function in any dll. 

For example:
     Baan4Obj.Timeout = 10         ' 10 seconds timeout
     Baan4Obj.ParseExecFunction "mydll", "myfunction(arg1, arg2)"
     If Baan4Obj.Error <> 0 Then MsgBox("An error occurred")
     MsgBox("The return value is " + Baan4Obj.ReturnValue)
Note:
      1.The property Timeout is used to prevent blocking on erroneous calls.
      2.Note that arguments to automation methods (ParseExecFunction) are not surrounded by brackets. 
         Note also how arguments are passed to myfunction. If arguments are passed by reference they might
         be changed by the function call, therefore the property FunctionCall will contain the modified arguments
         after the call. 
     3. The property Error contains the return status of the ParseExecFunction call while the property
         ReturnValue is the actual return value of the dll function. 
     4. If the DLL function returns a long this will be converted to a string in ReturnValue.
     5. Possible values for Error are:
                           0   success
                          -1   unknown dll name
                          -2   unknown function name
                          -3   syntax error in function call
                         -10   timer expired

To end an automation session the following example shows how to quit the BAAN application.
     Baan4Obj.Quit
      Set Baan4Obj = Nothing

Following are some limitations when using the OLE Automation interface:
• Due to the limitation in the 3GL function parse_and_exec_function, it will not be possible to supply arrays as arguments to a DLL function.
• DLL functions with a variable number of arguments or optional arguments are not supported.
• When a string is passed by reference, the result cannot become longer than the input string. So when a string must be filled then it should be initialized with enough spaces.
• The maximum size of the FunctionCall string is 4Kb (bshell limit).
• The maximum size of the ReturnValue string is 4Kb (bshell limit).

To implement this, we have to do two things:
1.  Implement DLL function(s) to obtain the names of the users from the database.
2.  Implement some Visual Basic code in MS-Excel.

DLL function example

The DLL below (called demodll) contains two functions to retrieve the names of the users from the database. One function to initiate the query and get the first name and another function to get the next name. When the last record has been returned an empty string will be returned.

      table     tttaad200
      long sql_id

      function extern string get_first_user()
      {
           string sql_query(512)
           string user(512)

           switch.to.company(0)
           sql_query = "select ttaad200.user from ttaad200"
           sql_id = sql.parse(sql_query)
           sql.exec(sql_id)
           e = sql.fetch(sql_id)

           if e = 0 
             then user = ttaad200.user
             else user = ""
           endif

           return( user )
      }

      function extern string get_next_user()
      {
           string user(512)
           e = sql.fetch(sql_id)

           if e = 0 
              then user = ttaad200.user
              else user = ""
           endif

           return( user )
      }

MS-Excel contains Visual Basic for Applications (VBA) that allows for scripting to control the Excel application, but also to control other applications through OLE Automation. The following is the Visual Basic source that will call the DLL functions mentioned above and display the results in a spreadsheet. The subroutine GetBaan4Users is an MS-Excel macro which can be invoked through user interface controls on the spreadsheet (buttons or menus).
      Dim user As String
      Dim Baan4Object As Object

      Sub GetBaan4Users()
          On Error GoTo CannotCreateBaan4

          ' run Baan4
          Set Baan4Object = CreateObject("Baan4.Application")

          On Error GoTo Baan4AutomationError

          ' get first user
          Baan4Object.ParseExecFunction "demodll", "get_first_user()"
          If (Baan4Object.Error <> 0) Then GoTo Baan4AutomationError
          user = Baan4Object.ReturnValue

          Row = 1
          Column = 1
          While (user <> "")
              ' fill the spreadsheet
              Worksheets("Main Sheet").Cells(Row, Column) = user
              Row = Row + 1
              If Row > 15 Then
Row = 1
Column = Column + 2
              End If

              ' get next user
              Baan4Object.ParseExecFunction "demodll", "get_next_user()"
              If (Baan4Object.Error <> 0) Then GoTo Baan4AutomationError
              user = Baan4Object.ReturnValue
          Wend

          ' exit Baan4
          Baan4Object.Quit
          Set Baan4Object = Nothing
          Exit Sub

      ' error handling
      CannotCreateBaan4:
          MsgBox "Unable to start Baan4"
          Exit Sub

      Baan4AutomationError:
          MsgBox "Baan4 automation error"
          Baan4Object.Quit
          Set Baan4Object = Nothing
          Exit Sub

      End Sub

The following example will fill an MS-Excel spreadsheet using BAAN SQL. To implement this a DLL, named ottdllsql_query, has been developed to parse and execute the query. To receive more information about this library, enter the following command: *> bic_info6.1 -eu ottdllsql_query [SHbic_info6.1 -eu ottdllsql_query]
DLL information
The DLL 'ottdllsql_query' contains:
• functions to parse, execute and stop a query
                      - olesql_parse
                      - olesql_fetch
                      - olesql_break
                      - olesql_close
• functions to import query results into Visual Basic
                      - olesql_getstring
                      - olesql_getint
                      - etc
• a function to convert an Easy SQL query to a string
                      - easysql_to_olesql
• declaration of external variables, as
                      - olesql_count
                      - olesql_float0, olesql_float1 ... olesql_float9
                          (double variables)
                      - olesql_int0, olesql_int1 ... olesql_int9 (long variables)
In the SAMPLES directory of BW an MS-Excel example is installed (BAAN4.XLS). This spreadsheet contains the following macros:
• The first macro, named GetBaanUsers, will show a number of Baan IV users on the spreadsheet (Worksheet 'Users'). Simply enter the button on the Worksheet 'Users' which starts this macro. For more information you are referred to the Visual Basic code to be found in Worksheet 'Module 1'.
• The second macro, named GetItemCount, will show all item groups in Baan IV with the numbers of items per item group on the spreadsheet (Worksheet 'Itemcount'). You can enter the button on the Worksheet "Itemcount' which starts this macro. To run this macro you have to insert an Easy SQL query in the Baan IV Application. To insert the query start the session -> Maintain Query Data (ttadv3180m000), insert a record named 'item' and choose the option "Maintain Query by Text Manager". In the text editor
                   enter the following query:
select   tiitm001.citg,           | Item group count(tiitm001.item)     | Item
from     tiitm001                 | Item data group by tiitm001.citg order by tiitm001.citg
                   For more information you are referred to the Visual Basic code
                   to be found in Worksheet 'Module 2'.